/**
 * 
 */
package org.starsource.forum.server.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.NoSuchElementException;
import java.util.Set;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import org.starsource.forum.client.exception.CategoryNotFoundException;
import org.starsource.forum.client.exception.DataInconsistantException;
import org.starsource.forum.client.exception.DatabaseException;
import org.starsource.forum.server.dao.CategoryDao;
import org.starsource.forum.server.domain.Category;

/**
 * @author james
 *
 */
public class CategoryDaoJdbc implements CategoryDao {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private static org.apache.log4j.Logger logger = org.apache.log4j.LogManager
	.getLogger(CategoryDaoJdbc.class.getName());

	private class GroupRowCallbackHandler implements RowCallbackHandler {
		private Set<Category> categorySet;
		private Category parent;
		
		public void processRow(ResultSet rs) throws SQLException {
			Category category = new Category();
			category.setId(rs.getInt("id"));
			category.setCaption(rs.getString("caption"));
			category.setName(rs.getString("name"));

			int parentId = rs.getInt("parent_id");
			if (parent == null) {
				category.setParentId(parentId);
			} else if (parentId == parent.getId()) {
				category.setParent(parent);
			} else {
				throw new SQLException("Data inconsistant exception",new DataInconsistantException());
			}

			categorySet.add(category);
			
			
		}
		
		public GroupRowCallbackHandler(Set<Category> categorySet) {
			this.categorySet = categorySet;
		}

		public GroupRowCallbackHandler(Set<Category> categorySet, Category parent) {
			this.categorySet = categorySet;
			this.parent = parent;
		}
	}
	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.CategoryDao#delete(org.starsource.forum.server.domain.Category)
	 */
	@Override
	public void delete(final Category category) throws DatabaseException {
		try {
			final String sql = "DELETE FROM t_category WHERE id = ?";
			jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setInt(1, category.getId());
					return ps;
				}
			});
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.CategoryDao#getCategory(int)
	 */
	@Override
	public Category getCategory(int id) throws CategoryNotFoundException,
			DatabaseException {
		try {
			String sql = "SELECT * FROM t_category WHERE id = ? ";
			final Set<Category> categorySet = new LinkedHashSet<Category>();
			jdbcTemplate.query(sql, new Object[] {id}, new GroupRowCallbackHandler(categorySet));
			Category category = categorySet.iterator().next();
			category.setChildren(this.getCategorySet(category));
			return category;
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		} catch (NoSuchElementException e) {
			logger.error(e.toString());
			throw new CategoryNotFoundException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.CategoryDao#getCategoryList()
	 */
	@Override
	public Set<Category> getCategorySet() throws DatabaseException {
		String sql = "SELECT * FROM t_category WHERE parent_id IS NULL ORDER BY caption ASC";
		final Set<Category> categorySet = new LinkedHashSet<Category>();
		jdbcTemplate.query(sql, new GroupRowCallbackHandler(categorySet));
		Iterator<Category> iterator = categorySet.iterator();
		while (iterator.hasNext()) {
			Category category = iterator.next();
			category.setChildren(getCategorySet(category));
		}
		return categorySet;
	}
	
	@Override
	public Set<Category> getCategorySet(final Category parent) throws DatabaseException {
		String sql = "SELECT * FROM t_category WHERE parent_id = ?";// ORDER BY caption ASC";
		final Set<Category> categorySet = new LinkedHashSet<Category>();
		jdbcTemplate.query(sql, new Object[] {parent.getId()}, new GroupRowCallbackHandler(categorySet, parent));
		Iterator<Category> iterator = categorySet.iterator();
		while (iterator.hasNext()) {
			Category category = iterator.next();
			category.setChildren(getCategorySet(category));
		}
		return categorySet;
	}
	
	

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.CategoryDao#save(org.starsource.forum.server.domain.Category)
	 */
	@Override
	public void save(final Category category) throws DatabaseException {
		final String sql = "INSERT INTO t_category(name, caption, parent_id) VALUES(?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 
				
				ps.setString(1, category.getName());
				
				ps.setString(2, category.getCaption());

				if (category.getParent() == null) {
					ps.setNull(3, java.sql.Types.INTEGER);
				} else {
					ps.setInt(3, category.getParent().getId());
				}
				return ps;
			}
		}, keyHolder);
		category.setId(keyHolder.getKey().intValue());
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.CategoryDao#update(org.starsource.forum.server.domain.Category)
	 */
	@Override
	public void update(final Category category) throws DatabaseException {
		final String sql = "UPDATE t_category set name = ?, caption = ?, parent_id = ?  WHERE id = ?";
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, category.getName());
				ps.setString(2, category.getCaption());
				if (category.getParent() == null) {
					ps.setNull(3, java.sql.Types.INTEGER);
				} else {
					ps.setInt(3, category.getParent().getId());
				}
				ps.setInt(4, category.getId());
				return ps;
			}
		});
	}

}